{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dressmaker - Easy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "List the post code, order number, order date and garment descriptions for all items associated with Ms Brown."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>post_code</th>\n",
       "        <th>order_no</th>\n",
       "        <th>order_date</th>\n",
       "        <th>description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>B2 5AB</td>\n",
       "        <td>7</td>\n",
       "        <td>2002-02-21</td>\n",
       "        <td>Short Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>B2 5AB</td>\n",
       "        <td>7</td>\n",
       "        <td>2002-02-21</td>\n",
       "        <td>Sundress</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>B2 5AB</td>\n",
       "        <td>7</td>\n",
       "        <td>2002-02-21</td>\n",
       "        <td>Suntop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>B2 5AB</td>\n",
       "        <td>9</td>\n",
       "        <td>2002-02-27</td>\n",
       "        <td>Shorts</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('B2 5AB', 7, '2002-02-21', 'Short Skirt'),\n",
       " ('B2 5AB', 7, '2002-02-21', 'Sundress'),\n",
       " ('B2 5AB', 7, '2002-02-21', 'Suntop'),\n",
       " ('B2 5AB', 9, '2002-02-27', 'Shorts')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT c_post_code post_code, order_no, order_date, description\n",
    "  FROM jmcust JOIN dress_order ON (\n",
    "      jmcust.c_no=dress_order.cust_no)  JOIN\n",
    "    order_line ON (\n",
    "        dress_order.order_no=order_line.order_ref) JOIN\n",
    "    garment ON (garment.style_no=order_line.ol_style)\n",
    "    WHERE c_name='Ms Brown'\n",
    "UNION ALL\n",
    "SELECT d_post_code post_code, order_no, order_date, description\n",
    "  FROM dressmaker JOIN construction ON (\n",
    "      dressmaker.d_no=construction.maker) JOIN\n",
    "    dress_order ON (dress_order.order_no=construction.order_ref) JOIN\n",
    "    order_line ON (dress_order.order_no=order_line.order_ref) JOIN\n",
    "    garment ON (garment.style_no=order_line.ol_style)\n",
    "    WHERE d_name='Ms Brown'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "List the customer name, postal information, order date and order number of all orders that have been completed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>c_name</th>\n",
       "        <th>c_post_code</th>\n",
       "        <th>order_date</th>\n",
       "        <th>order_no</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mrs Peacock</td>\n",
       "        <td>DD6 9NM</td>\n",
       "        <td>2002-01-10</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mrs Peacock</td>\n",
       "        <td>DD6 9NM</td>\n",
       "        <td>2002-01-10</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dr Green</td>\n",
       "        <td>SJ4 4WE</td>\n",
       "        <td>2002-01-11</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dr Green</td>\n",
       "        <td>SJ4 4WE</td>\n",
       "        <td>2002-01-11</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dr Green</td>\n",
       "        <td>SJ4 4WE</td>\n",
       "        <td>2002-01-11</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ms Muir</td>\n",
       "        <td>H2 7CV</td>\n",
       "        <td>2002-01-20</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mr Brass</td>\n",
       "        <td>FG24 9NM</td>\n",
       "        <td>2002-02-02</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mr Brass</td>\n",
       "        <td>FG24 9NM</td>\n",
       "        <td>2002-02-02</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ms White</td>\n",
       "        <td>E24 8PQ</td>\n",
       "        <td>2002-02-03</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ms White</td>\n",
       "        <td>E24 8PQ</td>\n",
       "        <td>2002-02-03</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ms White</td>\n",
       "        <td>E24 8PQ</td>\n",
       "        <td>2002-02-03</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Mrs Peacock', 'DD6 9NM', '2002-01-10', 1),\n",
       " ('Mrs Peacock', 'DD6 9NM', '2002-01-10', 1),\n",
       " ('Dr Green', 'SJ4 4WE', '2002-01-11', 2),\n",
       " ('Dr Green', 'SJ4 4WE', '2002-01-11', 2),\n",
       " ('Dr Green', 'SJ4 4WE', '2002-01-11', 2),\n",
       " ('Ms Muir', 'H2 7CV', '2002-01-20', 3),\n",
       " ('Mr Brass', 'FG24 9NM', '2002-02-02', 4),\n",
       " ('Mr Brass', 'FG24 9NM', '2002-02-02', 4),\n",
       " ('Ms White', 'E24 8PQ', '2002-02-03', 5),\n",
       " ('Ms White', 'E24 8PQ', '2002-02-03', 5),\n",
       " ('Ms White', 'E24 8PQ', '2002-02-03', 5)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT c_name, c_post_code, order_date, order_no\n",
    "  FROM jmcust JOIN dress_order ON (\n",
    "      jmcust.c_no=dress_order.cust_no)  JOIN\n",
    "    order_line ON (dress_order.order_no=order_line.order_ref)\n",
    "    WHERE completed='Y'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "Which garments have been made or are being made from 'red abstract' or 'blue abstract' coloured materials."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>style_no</th>\n",
       "        <th>description</th>\n",
       "        <th>colour</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Long Skirt</td>\n",
       "        <td>Red Abstract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Long Skirt</td>\n",
       "        <td>Blue Abstract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Sundress</td>\n",
       "        <td>Red Abstract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Short Skirt</td>\n",
       "        <td>Blue Abstract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Trousers</td>\n",
       "        <td>Red Abstract</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Long Skirt', 'Red Abstract'),\n",
       " (2, 'Long Skirt', 'Blue Abstract'),\n",
       " (5, 'Sundress', 'Red Abstract'),\n",
       " (4, 'Short Skirt', 'Blue Abstract'),\n",
       " (1, 'Trousers', 'Red Abstract')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT style_no, description, colour\n",
    "  FROM garment JOIN order_line ON (\n",
    "      garment.style_no=order_line.ol_style) JOIN\n",
    "    material ON (\n",
    "        order_line.ol_material=material.material_no)\n",
    "    WHERE LOWER(colour) IN ('red abstract', 'blue abstract')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "How many garments has each dressmaker constructed? You should give the number of garments and the name and postal information of each dressmaker."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>d_no</th>\n",
       "        <th>d_name</th>\n",
       "        <th>d_post_code</th>\n",
       "        <th>n</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Mrs Hem</td>\n",
       "        <td>A12 6BC</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Miss Stitch</td>\n",
       "        <td>DF4 7HJ</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Mr Needles</td>\n",
       "        <td>E12 6LG</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Ms Sew</td>\n",
       "        <td>EF7 9KL</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Mr Seam</td>\n",
       "        <td>H45 7YH</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Mr Taylor</td>\n",
       "        <td>SH6 9RT</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Miss Pins</td>\n",
       "        <td>B4 9BL</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Mrs Hem', 'A12 6BC', 14),\n",
       " (2, 'Miss Stitch', 'DF4 7HJ', 20),\n",
       " (3, 'Mr Needles', 'E12 6LG', 12),\n",
       " (4, 'Ms Sew', 'EF7 9KL', 12),\n",
       " (5, 'Mr Seam', 'H45 7YH', 12),\n",
       " (6, 'Mr Taylor', 'SH6 9RT', 7),\n",
       " (7, 'Miss Pins', 'B4 9BL', 11)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT d_no, d_name, d_post_code, COUNT(*) n\n",
    "  FROM dressmaker JOIN construction ON (\n",
    "      dressmaker.d_no=construction.maker) JOIN\n",
    "    dress_order ON (dress_order.order_no=construction.order_ref) JOIN\n",
    "    order_line ON (order_line.order_ref=dress_order.order_no) JOIN\n",
    "    garment ON (garment.style_no=order_line.ol_style)\n",
    "    GROUP BY d_no, d_name, d_post_code"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "Give the names of those dressmakers who have finished items made from silk for completed orders."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>d_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Miss Pins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Miss Stitch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mr Needles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mrs Hem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ms Sew</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Miss Pins',), ('Miss Stitch',), ('Mr Needles',), ('Mrs Hem',), ('Ms Sew',)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT d_name\n",
    "  FROM dressmaker JOIN construction ON (dressmaker.d_no=construction.maker) JOIN\n",
    "    dress_order ON (dress_order.order_no=construction.order_ref) JOIN\n",
    "    order_line ON (order_line.order_ref=dress_order.order_no) JOIN\n",
    "    garment ON (garment.style_no=order_line.ol_style) JOIN\n",
    "    material ON (material.material_no=order_line.ol_material)\n",
    "    WHERE LOWER(fabric)='silk' AND completed='Y'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
